﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using DVSProject.Models;
namespace DVSProject.Lib
{
    public class ModelRepo
    {
        public const string m_msg_head = "Model";

        public IEnumerable<ModelModels> GetModel()
        {
            List<ModelModels> Models = new List<ModelModels>();
            string SQLCommand = @"select * from TBModel where Status <>'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        ModelModels model = new ModelModels();
                        model.ModelId = (int)reader["ModelId"];
                        model.NameTh = (string)reader["NameTh"];
                        model.NameEng = (string)reader["NameEng"];
                        model.RentBasic = Convert.ToDecimal(reader["RentBasic"]);
                        model.RentForUse = Convert.ToDecimal(reader["RentForUse"]);
                        model.ConversionRate = (int)reader["ConversionRate"];
                        model.Status = (string)reader["Status"];
                        model.CreateBy = (string)reader["CreateBy"];
                        model.CreateOn = (DateTime)reader["CreateOn"];
                        model.UpdateBy = (string)reader["UpdateBy"];
                        model.UpdateOn = (DateTime)reader["UpdateOn"];

                        Models.Add(model);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Models.ToList();
        }

        public bool Create(ModelModels model)
        {
            bool result = true;
            string SQLCommand = @"insert TBModel 
                                    values (@NameTh,@NameEng,@RentBasic,@RentForUse,@ConversionRate,@Status,@UserId,GETDATE(),
                                    @UserId,GETDATE())";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@NameTh", model.NameTh);
                    db.AddParameter("@NameEng", model.NameEng);
                    db.AddParameter("@RentBasic", model.RentBasic);
                    db.AddParameter("@RentForUse", model.RentForUse);
                    db.AddParameter("@ConversionRate", model.ConversionRate);
                    db.AddParameter("@Status", "Active");
                    db.AddParameter("@UserID", model.CreateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public ModelModels GetModelById(int id)
        {
            ModelModels model = new ModelModels();
            string SQLCommand = @"select * from tbModel where ModelId=@Id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@Id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        model.ModelId = (int)reader["ModelId"];
                        model.NameTh = (string)reader["NameTh"];
                        model.NameEng = (string)reader["NameEng"];
                        model.RentBasic = Convert.ToDecimal(reader["RentBasic"]);
                        model.RentForUse = Convert.ToDecimal(reader["RentForUse"]);
                        model.ConversionRate = (int)reader["ConversionRate"];
                        model.Status = (string)reader["Status"];
                        model.CreateBy = (string)reader["CreateBy"];
                        model.CreateOn = (DateTime)reader["CreateOn"];
                        model.UpdateBy = (string)reader["UpdateBy"];
                        model.UpdateOn = (DateTime)reader["UpdateOn"];
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return model;
        }

        public bool Edit(ModelModels model)
        {
            bool result = true;
            string SQLCommand;
            SQLCommand = @"Update TBModel 
                                    Set NameTh=@NameTh,
                                        NameEng=@NameEng,
                                        RentBasic=@RentBasic,
                                        RentForUse=@RentForUse,
                                        ConversionRate=@ConversionRate,UpdateBy=@UserId,UpdateOn=getdate()
                                    where ModelId=@ModelId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@ModelId", model.ModelId);
                    db.AddParameter("@NameTh", model.NameTh);
                    db.AddParameter("@NameEng", model.NameEng);
                    db.AddParameter("@RentBasic", model.RentBasic);
                    db.AddParameter("@RentForUse", model.RentForUse);
                    db.AddParameter("@ConversionRate", model.ConversionRate);
                    db.AddParameter("@UserID", model.UpdateBy);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;

        }

        public bool Delete(ModelModels model)
        {
            bool result = true;
            string SQLCommand = @"Update TBModel 
                                    Set Status=@Status,UpdateBy=@UserId,UpdateOn=getdate()
                                    where ModelId=@ModelId";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();

                    db.AddParameter("@ModelId", model.ModelId);
                    db.AddParameter("@Status", "Delete");
                    db.AddParameter("@UserID", model.UpdateBy );
                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}